/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.dusin.servlet.common;

import com.tlf.dyjc5.dao.CommonComboDao;
import com.tlf.dyjc5.service.YsjService;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItemIterator;
import org.apache.commons.fileupload.FileItemStream;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.Streams;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.dusin.commonutil.CommonUtil;
import org.dusin.commonutil.DateUtil;
import org.dusin.db.exception.AlreadLockedException;
import org.dusin.web.util.JsonUtil;

import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

/**
 * 上传导入月数据
 *
 * @author tengyu
 */
public class UploadImportExcelServlet extends HttpServlet {

    static Logger logger = Logger.getLogger(UploadImportExcelServlet.class);

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String savePath = this.getInitParameter("SavePath");        
        try {
            /*SmartUpload smartUpload = new SmartUpload();
             javax.servlet.jsp.PageContext pageContext = javax.servlet.jsp.JspFactory.getDefaultFactory().getPageContext(this, request, response, null, true, 8192, true);
             smartUpload.initialize(pageContext);
             smartUpload.upload();
             System.out.println(savePath);
             smartUpload.save(savePath);
             //smartUpload.getFiles().getFile(0).*/

            //logger.debug("进入servlet");
            List removeDataList = new ArrayList();
            List createDataList = new ArrayList();

            ApplicationContext context = WebApplicationContextUtils.getWebApplicationContext(getServletContext());
            YsjService ysjService = (YsjService) context.getBean("ysjService");
            CommonComboDao commonDao = (CommonComboDao) context.getBean("commonDao");

            String currentOrgCode = (String) request.getSession().getAttribute("currentOrgCode");
            String currentYear=null;
            String currentMonth=null;
            String lock="Lock";

            Map pointMaps = createMap(commonDao, "id", "name", "name,deviceId,orgCode,pointArea,pointPosition,pointTypeId,pointType2Id,voltageLevelId,lowerLimit,upperLimit,code", "t_point", "where  orgCode like '" + currentOrgCode + "%'");
            Map pointTypeMaps = createMap(commonDao, "id", "id", "name", "t_pointType", "where orgCode='01' or orgCode='" + currentOrgCode + "'");
            Map pointType2Maps = createMap(commonDao, "id", "id", "name", "t_pointType2", "where orgCode='01' or orgCode='" + currentOrgCode + "'");
            Map orgMaps = createMap(commonDao, "orgCode", "orgCode", "name,orgTypeId,code", "t_org", "where orgCode like '" + currentOrgCode + "%'");
            Map orgTypeMaps = createMap(commonDao,"id","id","name","t_orgType","");
            Map voltageLevelMaps = createMap(commonDao, "id", "id", "name,voltage", "t_voltageLevel", "");           


            boolean isMultipart = ServletFileUpload.isMultipartContent(request);
            //System.out.println("isMultipart:" + isMultipart);

            // Create a factory for disk-based file items
            DiskFileItemFactory factory = new DiskFileItemFactory();

// Configure a repository (to ensure a secure temp location is used)
            ServletContext servletContext = this.getServletConfig().getServletContext();
            File repository = (File) servletContext.getAttribute("javax.servlet.context.tempdir");
            //System.out.println("repository:" + repository.getAbsolutePath());
            factory.setRepository(repository);

// Create a new file upload handler
            ServletFileUpload upload = new ServletFileUpload(factory);
            upload.setHeaderEncoding("utf-8");  //可以解决中文文件名乱码问题

// Parse the request
            //List<FileItem> items = upload.parseRequest(request);

            // Parse the request
            FileItemIterator iter = upload.getItemIterator(request);

            StringBuilder sb = new StringBuilder();
            StringBuilder successNos = new StringBuilder();
            int i = 0;
            int count = 0;
            while (iter.hasNext()) {
                FileItemStream item = iter.next();
                String name = item.getFieldName();
                InputStream stream = item.openStream();
                if (item.isFormField()) {
                    if ("date".equals(name)) {
                        //System.out.println(Streams.asString(stream, "utf-8"));

                        String date = Streams.asString(stream, "utf-8");
                        currentYear = date.substring(0, 4);
                        currentMonth = date.substring(5);
                    }else if("lock".equals(name)){
                        lock = Streams.asString(stream, "utf-8");
                    }
                    //System.out.println("Form field " + name + " with value "
                    //        + Streams.asString(stream) + " detected.");
                } else {
                    
                    // Process the input stream
                    logger.info("File field " + name + " with file name "
                            + item.getName() + " detected.");

                    Workbook wb = new HSSFWorkbook(stream);
                    boolean hasNext = true;

                    Sheet sheet1 = wb.getSheetAt(0);


                    while (hasNext) {
                        Row r = sheet1.getRow(++i);     //从Excel表的第二行开始，序号为1
                        if (r == null) {
                            hasNext = false;
                            break;
                        }
                        Cell cell = r.getCell(2);
                        String pointName = getStringValue(cell);
                        logger.debug("监测点名称:"+pointName);
                        if (pointName == null || "".equals(pointName.trim())) {    //若无检测点名称，表示行结束，所以导入数据Excel表格中不能有空行，否则空行后的数据不导入
                            hasNext = false;
                            break;
                        }
                        //根据监测点名称获取pointId 和deviceId
                        Map map = (Map) pointMaps.get(pointName.trim().toLowerCase());
                        if (map == null) {
                            logger.debug(pointName+"不存在");
                            sb.append(i).append("-").append(pointName + "在当前单位中不存在").append("<br/>");                            
                            continue;
                        }

                        
                        String orgCode = (String)map.get("orgCode");
                        String pointCode = (String)map.get("code");
                        String company = getStringValue(r.getCell(0));
                        
                        String subCompany=getStringValue(r.getCell(1));
                        
                        Map stationMap =(Map)orgMaps.get((orgCode));
                         String companyCode = stationMap.get("code").toString();
                        String station = null;
                        if(stationMap!=null)
                            station = (String)stationMap.get("name");
                        
                        Long pointId = (Long) map.get("id");
                        String deviceId = map.get("deviceId").toString();                        
                       
                        String pointArea = (String)map.get("pointArea");
                        String pointPosition = (String)map.get("pointPosition");
                        
                        Long pointTypeId = (Long)map.get("pointTypeId");
                        Map pointTypeMap=null;
                        if(pointTypeId!=null){
                            pointTypeMap=(Map)pointTypeMaps.get(pointTypeId.toString());
                        }else{
                            pointTypeId = 0L;
                        }
                        String pointType=null;
                        if(pointTypeMap!=null){
                            pointType = (String)pointTypeMap.get("name");
                        }else{                            
                            sb.append(i).append("-").append("未知监测点类别").append("<br/>");                            
                            continue;
                        }                        
                        Long pointType2Id = (Long)map.get("pointType2Id");
                        if(pointType2Id==null){
                            pointType2Id = 0L;
                        }
                        Map pointType2Map=(Map)pointType2Maps.get(pointType2Id.toString());
                        String pointType2 = null;
                        if(pointType2Map!=null)
                            pointType2=(String)pointType2Map.get("name");
                        
                        BigDecimal lowerLimit =(BigDecimal) map.get("lowerLimit");
                        BigDecimal upperLimit=(BigDecimal)map.get("upperLimit");
                        
                        Long orgTypeId = (Long)stationMap.get("orgTypeId");
                        if(orgTypeId == null)
                            orgTypeId = 0L;
                        Map orgTypeMap = null;                        
                        orgTypeMap = (Map)orgTypeMaps.get(orgTypeId.toString());
                        
                        String orgTypeName =null;
                        if(orgTypeMap!=null)
                            orgTypeName=(String)orgTypeMap.get("name");
                        
                        String statusStr = getStringValue(r.getCell(18));
                        int pointStatus = 11;
                        if("观测".equals(statusStr)){
                            pointStatus=01;
                        }
                        
                        Long voltageLevelId = (Long)map.get("voltageLevelId");
                        if(voltageLevelId ==null)
                            voltageLevelId = 0L;
                        Map voltageLevelMap=(Map)voltageLevelMaps.get(voltageLevelId.toString());
                        String voltageLevelName = (String)voltageLevelMap.get("name");
                        int voltage=0;
                        BigDecimal voltageLevelValue=(BigDecimal)voltageLevelMap.get("voltage");
                        
                        try {

                            cell = r.getCell(8);
                            if (cell == null) {
                                continue;
                            }
                            String totalRunMin = getStringValue(cell);

                            String aboveUpperLimitMin = getStringValue(r.getCell(11));;
                            String underLowerLimitMin = getStringValue(r.getCell(12));
                            String avgVoltage = getStringValue(r.getCell(13));
                            String minVoltage = getStringValue(r.getCell(14));
                            String minVoltageTime = getStringValue(r.getCell(15));
                            String maxVoltage = getStringValue(r.getCell(16));
                            String maxVoltageTime = getStringValue(r.getCell(17));
                            String dataTime = getStringValue(r.getCell(19));
                            String dataYear = dataTime.substring(0, 4);
                            String dataMonth = dataTime.substring(5, 7);
                            if(currentYear==null||currentMonth==null||!currentYear.equals(dataYear)||!currentMonth.equals(dataMonth)){
                                sb.append(i).append("-").append( "数据日期不是设定的日期").append("<br/>");
                                continue;
                            }
                            String reasonAnalyze = getStringValue(r.getCell(20));
                            String treatWay = getStringValue(r.getCell(21));
                            String completeCase = getStringValue(r.getCell(22));
                            String note = getStringValue(r.getCell(23));



                            removeDataList.add(new Object[]{pointId, dataYear, dataMonth});
                            createDataList.add(new Object[]{0, deviceId, pointId, dataYear, dataMonth, 
                                null, DateUtil.get(), 5, totalRunMin, aboveUpperLimitMin, underLowerLimitMin, 
                                maxVoltage, maxVoltageTime, minVoltage, minVoltageTime, avgVoltage, note, 0, 0, 0, 0, reasonAnalyze, treatWay, completeCase,
                                orgCode,company,subCompany,station,pointName,pointArea,pointPosition,pointTypeId,pointType,pointType2Id,pointType2,
                                lowerLimit,upperLimit,pointStatus,orgTypeId,orgTypeName,voltage,voltageLevelValue,voltageLevelId,voltageLevelName,pointCode,companyCode});
                            count++;
                            successNos.append(i).append(",");
                            logger.debug("deviceId:"+deviceId+",pointId:"+pointId+",dataYear:"+dataYear+",dataMonth:"+dataMonth+",maxVoltage:"+maxVoltage+",maxVoltageTime:"+maxVoltageTime+",minVoltage:"+minVoltage+",minVoltageTime:");
                        } catch (Exception e) {  //跳过不完整数据行
                            logger.debug(i+"跳过不完整的行");
                            sb.append(i).append("-").append(e.getMessage()).append("<br/>");
                        }
                    }
                    sb.insert(0, "处理记录总行数:" + (i - 1) + ",成功入库:" + count + "<br/>");
                    if (successNos.length() > 0) {
                        successNos.deleteCharAt(successNos.length() - 1);
                        sb.insert(1, "成功导入的行号:" + successNos.toString() + "<br/>");    //成功导入的行号
                    } else {
                        sb.insert(1, "成功导入的行号:0" + "<br/>");    //成功导入的行号
                    }
                }
            }
            if(ysjService.isLock(currentOrgCode, Integer.parseInt(currentYear), Integer.parseInt(currentMonth), lock)){                
                throw new AlreadLockedException();
            }
            logger.debug("3000");
            ysjService.trAddAll(removeDataList, createDataList);
            logger.warn(sb.toString());
            //Thread.sleep(30000);
            out.println(JsonUtil.createSuccessJsonResponse(sb.toString()));
        } catch (AlreadLockedException e) {            
            logger.error("1000"+e.getMessage());
            out.println(JsonUtil.createFailureJsonResponse(e.getMessage()));
        }catch (Exception e) {
            e.printStackTrace();
            logger.error("2000"+e.getMessage());
            out.println(JsonUtil.createFailureJsonResponse(e.getMessage()));
        } finally {
            out.close();
        }
    }

    private static Map createMap(CommonComboDao commonDao, String id, String name, String columns, String tableName, String condition) {
        List<Map<String, Object>> recordList = commonDao.findInfoWithCondition(id, columns, tableName, condition);
        Map<String, Object> resultMap = new HashMap<String, Object>();
        for (Map map : recordList) {     //将List转换成键值对（name->id）
            resultMap.put((map.get(name).toString()).toLowerCase(), map);
        }
        return resultMap;
    }

    private String getStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return (cell.getRichStringCellValue().getString());

            case Cell.CELL_TYPE_NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    return DateUtil.get(cell.getDateCellValue());
                } else {
                    return CommonUtil.formatNumber(cell.getNumericCellValue(), "#.#");
                }

            case Cell.CELL_TYPE_BOOLEAN:
                return Boolean.toString(cell.getBooleanCellValue());

            case Cell.CELL_TYPE_FORMULA:
                return (cell.getCellFormula());

            default:
                return null;
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
